# requests pandas openpyxl
import requests
import pandas as pd
import time
import re
import atexit

url = 'http://119.164.252.44:8001/data'
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3538.25 Safari/537.36 Core/1.70.3861.400 QQBrowser/10.7.4313.400'
}
name_re = re.compile('^\d+-\d+$')
datalist=[]

@atexit.register
def clean():
    quchong(datalist)
    
def get_list(cantonId, pageSize, targetStatus, k):
    data = {
        'cantonId': cantonId,   # 区域
        'module': 'portal',
        'service': 'Query',
        'method': 'targetOfNotice2',
        'targetStatus': targetStatus,  # 交易状态
        'pageNum': 1,
        'pageSize': pageSize    # 获取最大数
    }
    resp = requests.post(url, data=data, headers=headers)
    data1 = resp.json()['notice_data']
    for i1 in data1:
        area = i1['area']
        for i2 in i1['list']:
            ks = 0
            if len(k) > 0:
                for kk in k:
                    if '2019' not in i2['begin_apply_time']:   # 只有2019年在济南中
                        break
                    if kk in i2['goods_name']:
                        ks = 1
                        break
            else:
                ks = 1
            if ks == 1:
                get_info(i2["target_id"],area)

# 处理不全的容积率、建筑密度范围
def data_format(a,b,c,d,s):
    if a==None:
        a=''
        b=''
    if d==None:
        d=''
        c=''
    if a=='' and d=='':
        return ''
    else:
        return f"{a}{b}{s}{c}{d}"

def get_info(target_id,area):
    data = {
        'module': 'portal',
        'service': 'Query',
        'method': 'queryTarget',
        'targetId': target_id
    }
    resp = requests.post(url, data=data, headers=headers)
    data2 = resp.json()
    goods=data2['goods']
    goods_num = len(goods)
    for i1 in goods:
        try:
            if goods_num > 1:
                goods_name = f"{i1['goods_name'].strip()}({goods_num}宗地共同)({i1['target_id'].strip()})"
            else:
                goods_name = f'{i1["goods_name"].strip()}'
                if name_re.match(goods_name) != None:
                    goods_name=f'="{goods_name}"'
            save_url=f'http://119.164.252.44:8001/portal/noticeDetail.html?targetId={target_id}'    
            goods_no = f'=HYPERLINK("{save_url}","{i1["goods_no"].strip()}")'            # 宗地编号
            status=i1['status']      # 交易状态码
            target_name= target_name_list[status-3]  # 交易状态
            if data2['target']['is_stop']==1:
                target_name='已终止'
            end_trans_time = i1['end_trans_time']   # 成交时间
            if end_trans_time==None:
                end_trans_time=''
            else:
                end_trans_time = end_trans_time.strip().split()[0]    # 成交时间
            address=i1['address'].strip()                    # 位置
            goods_use=i1['goods_use'].strip()                #用途
            use_years=i1['use_years']                # 年期
            land_area=i1['land_area']                # 土地面积
            goods_begin_price=i1['begin_price']/10000  # 起拍价
            trans_price=data2['target']['trans_price']         # 成交价
            if trans_price==None:
                trans_price=''
            else:
                trans_price/=10000
            trans_bidder=data2['target']['trans_bidder']    # 成交单位
            if trans_bidder==None:
                trans_bidder=''
            
            di_shang=data_format(i1['plot1_down'],i1['plot1_flag'],i1['plot1_flag2'],i1['plot1_up'],'地上')
            # 地下容积率处理
            di_xia=data_format(i1['plot2_down'],i1['plot2_flag'],i1['plot2_flag2'],i1['plot2_up'],'地下')
            # 建筑密度处理
            jian_zhu_mi_du=data_format(i1['build_density_down'],i1['build_density_flag'],i1['build_density_flag2'],i1['build_density_up'],'建筑密度')
        except:
            continue
        try:
            land_area= float(land_area)              # 面积
            mu_shu=round(land_area*0.0015,1)         # 亩数
        except:
            land_area= ''
            mu_shu=''
        try:
            dan_jia=round(trans_price/land_area*10000,1)  # 单价（元/平方米） # 已成交：单价
            mei_mu_jia=round(dan_jia/15,1)          # 每亩价格（万元/亩）     # 已成交：每亩价格
        except:
            try:
                dan_jia=round(goods_begin_price/land_area*10000,1)    # 未成交： 挂牌单价
                mei_mu_jia=round(dan_jia/15,1)                        # 未成交：每亩价格
            except:
                dan_jia=''                                            # 数据不正确时，为空
                mei_mu_jia=''
        try:
            lou_mian= round(dan_jia/float(i1['plot1_up']),1)   # 楼面地价
        except:
            lou_mian= ''
        datalist.append([target_name,area,goods_no,end_trans_time,goods_name,address,\
            goods_use,use_years,land_area,mu_shu,goods_begin_price,trans_price,\
                dan_jia,mei_mu_jia,lou_mian,di_shang,di_xia,jian_zhu_mi_du,trans_bidder])
        print(goods_name, flush=True)

def quchong(datalist):
    df = pd.DataFrame(datalist,columns=['交易状态','区域','宗地编号（点击进入详情页）','成交时间','宗地名称','土地位置','用途',\
        '年期','面积(平方米)','亩数','起拍价(万元)','成交价(万元)',\
            '单价(元/平方米)','每亩价格(万元/亩)','楼面地价(元/建筑平方米)',\
                '地上容积率','地下容积率','建筑密度','成交单位'])
    dtt = df.drop_duplicates()
    # dtt.to_csv(fname,encoding='utf-8_sig',index=False)
    efname=f'{fname}.xlsx'
    dtt.to_excel(efname,index=False)
    print('完成')

if __name__ == '__main__':
    cantonId_list = [
        '',   # 全部
        '370100',    # 济南市
        '370118',    # 莱芜区
        '370116',    # 莱芜高新区
        '370119',    # 钢城区
        '370181',    # 章丘区
        '370124',    # 平阴县
        '370125',    # 济阳区
        '370126',    # 商河县
        '370117'    # 济南起步区
        ]
    canton_list=['全部','济南区','莱芜区','莱芜高新区','钢城区','章丘区','平阴县','济阳区','商河县','济南起步区']
    target_name_list=['正公告','交易中','已成交','已流拍','待审核']
    target_status_list=[3,4,5,6,7]
    try:
        target_id=int(input('------------------------------------------------------------------\n\
1.正公告  2.交易中  3.已成交  4.已流拍  5.待审核  其他输入为全部\n\
------------------------------------------------------------------\n\
请选择交易状态（1-5）：'))
        targetStatus=target_status_list[target_id-1]
        targetName=target_name_list[target_id-1]
        if target_id not in [1,2,3,4,5]:
            raise
        print(f'您的选择：{targetName}\n')
    except:
        targetStatus=''
        targetName='全部交易'
        print('您的选择：全部\n')
      # 空：全部，3：正公告，4：交易中，5：已成交，6：已流拍，7：待审核
    pageSize = 1000   # 一次请求的数据数量
    try:
        canton_i=int(input('-----------------------------------------------------\n\
1.全部  2.济南  3.莱芜  4.莱芜高新  5.钢城\n\
6.章丘  7.平阴  8.济阳  9.商河  10.济南先行区\n\
其他输入-->莱芜、钢城\n\
-----------------------------------------------------\n\
请输入数字选择区域：'))
        cantonId = cantonId_list[canton_i-1]
    except:
            fname = f'招拍挂交易实例（莱芜+钢城-{targetName}）{int(time.time())}'
            try:
                get_list('370118', pageSize, targetStatus, k=[])  # 莱芜
                get_list('370116', pageSize, targetStatus, k=[])  # 莱芜高新
                get_list('370119', pageSize, targetStatus, k=[])  # 钢城
                k = ['莱芜', '莱高', '钢城']
                get_list('370100', pageSize, targetStatus, k=k)  # 2019公告在济南中
            except:
                pass
    else:    
        fname=f'招拍挂交易实例（{canton_list[canton_i-1]}-{targetName}）{int(time.time())}'
        try:
            get_list(cantonId,pageSize,targetStatus,k=[])
        except:
            pass